/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.garagesale.data;

import com.garagesale.config.hibernate.HibernateUtil;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Hibernate;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Collections;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.engine.query.FilterQueryPlan;

/**
 *
 * @author christophe
 */
public class DBAccess
{

    private DBAccess()
    {
        // enforce singleton pattern
    }
    private static final DBAccess INSTANCE = new DBAccess();

    public static DBAccess getInstance()
    {
        return INSTANCE;
    }

    public List<User> retrieveAllUsers()
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        List result = session.createQuery("from User").list();
        session.close();

        return (List<User>) result;
    }

    /**
     * Retrieves the user based on the username.
     * Returns null if no user was found
     */
    public User getUserByUsername(String username)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        Query query = session.createQuery("from User u where u.username = :username");
        query.setString("username", username);
        List<?> result = query.list();

        session.close();

        if (result.size() > 0)
        {
            return (User) result.get(0);
        }

        return null;
    }

    public ShoppingCart getShoppingCartByUserId(int pUserId)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();

        Query hql = session.createQuery(
                "from ShoppingCart cart where cart.user.userID = :pUserId");
        hql.setInteger("pUserId", pUserId);

        List<?> result = hql.list();

        session.close();

        if (result.size() > 0)
        {
            return (ShoppingCart) result.get(0);
        }

        return null;
    }

    public ShoppingCart getShoppingCartBySessionID(String pSessionId)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        Query q = session.createQuery(
                "from ShoppingCart sc where sc.sessionId = :sessionID");
        q.setString("sessionID", pSessionId);

        List result = q.list();
        session.close();

        if (result.size() > 0)
        {
            return (ShoppingCart) result.get(0);
        }

        return null;
    }

    public List<User> retrieveAllAdmins()
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        List result = session.createCriteria(User.class).add(Restrictions.eq("userRole", "admin")).list();
        session.close();

        return (List<User>) result;
    }

    public List<Item> findAvailableItemsByCategory(ItemCategory category)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        Query q = session.createQuery(
                "from Item i where i.itemCategory.itemCategoryID = :categoryId");
        q.setInteger("categoryId", category.getItemCategoryID());

        List result = q.list();
        session.close();
    
        return (List<Item>)result;
    }

    public  List<Item> seachItems(String itemName, Integer categoryID, String description, BigDecimal lowPrice, BigDecimal highPrice, boolean onlyAvailable)
    {

        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();

        Criteria c = session.createCriteria(Item.class);

        if (null != itemName)
        {
            c = c.add(Restrictions.like("name", itemName));
        }
        if (null != description)
        {
            c = c.add(Restrictions.like("description", description));
        }
        if (null != categoryID)
        {
            c = c.add(Restrictions.eq("itemCategory.itemCategoryID", categoryID));
        }

        if (null != lowPrice)
        {
            c = c.add(Restrictions.ge("salePrice", lowPrice));
        }

        if (null != highPrice)
        {
            c = c.add(Restrictions.le("salePrice", highPrice));
        }
 
        // only display items that are neither in shopping carts, or have already been sold
        if (onlyAvailable)
        {
            c = c.add(Restrictions.isNull("shoppingCart"));
            c = c.add(Restrictions.isNull("userByBuyerUserId"));
        }
 

        List result = c.list();
        session.close();

        return (List<Item>) result;
    }

    /**
     * Retrieves all categories, but without their associated items
     * @return
     */
    public List<ItemCategory> getAllCategories()
    {
        return retrieveAll(
                "select new ItemCategory(ic.itemCategoryID, ic.name, "
                + "ic.labelColor, ic.imageLocation) from ItemCategory as ic");
//        return retrieveAll("from ItemCategory");
    }

    public void update(Serializable toUpdate)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        session.update(toUpdate);
        session.getTransaction().commit();
        session.close();
    }

    public <T> T retrieveByID(Class<T> classType, Serializable toRetrieve)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Object result = session.get(classType, toRetrieve);

        session.close();

        return (T) result;
    }

    public int create(Serializable toCreate)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();

        session.beginTransaction();
        Object created = session.save(toCreate);
        session.getTransaction().commit();
        session.close();

        return (Integer) created;
    }

    public void delete(Serializable toDelete)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();

        session.beginTransaction();
        session.delete(toDelete);
        session.getTransaction().commit();
        session.close();
    }

    public List<Item> getAllItems()
    {
        return retrieveAll("from Item");
    }

    /**
     * Takes an HQL query without parameters, and returns a list of whatever is expected.
     * For an example of usage, see {@link DBAccess#getAllCategories()}
     * @param <T>
     * @param query
     * @return
     */
    public <T> List<T> retrieveAll(String query)
    {
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        Query q = session.createQuery(query);
        List result = q.list();
//
//        for (Object o : result)
//        {
//            Hibernate.initialize(o);
//        }

        session.close();

        return (List<T>) result;
    }


    public void delete(Object object)
    {
         Session session = HibernateUtil.getSessionFactory().openSession();
         session.beginTransaction();
         session.delete(object);
         session.getTransaction().commit();
         session.close();
    }

    public String getDateTimeString()
    {
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date();
        return dateFormat.format(date);
    }

    public Date getDateTime()
    {
        Date date = new Date();
        return date;
    }
}
